﻿using Aliyun.OSS;
using Infrastructure.Attribute;
using JTZY.Model.Agent.Dto;
using JTZY.Model.Opdata.Dto;
using JTZY.Model.Order;
using Microsoft.Extensions.Logging;
using SqlSugar.SplitTableExtensions;
using StackExchange.Redis;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.CompilerServices;
using System.Text;
using System.Threading.Tasks;

namespace JTZY.Service.Opdata
{
    [AppService(ServiceType = typeof(ISaleDataService), ServiceLifetime = LifeTime.Transient)]
    public class SaleDataService : BaseService<Billaftersaledetail>, ISaleDataService
    {
        public List<SaleTotalByTimeTypeModel> GetSaleSummaryByTimeType(int timeType, long userId)
        {
            var sb = new StringBuilder();
            if (timeType == 0)
            {
                sb.Append(@$"WITH RECURSIVE date_list AS (
                                 SELECT CURDATE() AS date
                                 UNION ALL
                                 SELECT date - INTERVAL 1 DAY
                                 FROM date_list
                                 WHERE date > CURDATE() - INTERVAL 1 YEAR
                             )
                             select DATE_FORMAT(d.date, '%Y-%m-%d') as time,IFNULL(amt.saleamt,0) as sellAmount,IFNULL(amt.profit,0) as profitAmount 
                             from date_list as d
                             left join (
                             select 
                             sum(c.amount) as profit,
                             sum(c2.goodsAmount) as saleAmt,
                             DATE_FORMAT(c.createTime, '%Y-%m-%d')  as time
                             from  corecmsagentorder c 
                             inner join corecmsorder c2 
                             on c.orderId = c2.orderId 
                             where c.isSettlement=1
                             and c.isDelete=0
                             and c.createTime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                             and c.createTime<=CONCAT(CURDATE(), ' ', MAKETIME(23, 59, 59))
                             and c.UserId={userId}
                             group by DATE_FORMAT(c.createTime, '%Y-%m-%d')
                             
                             ) as amt on d.date=amt.`time`
                             ");
            }
            else if (timeType == 1) //按月
            {
                sb.Append($@"WITH RECURSIVE month_list AS (
                                SELECT DATE_FORMAT(CURDATE(), '%Y-%m-01') AS month_start
                                UNION ALL
                                SELECT DATE_SUB(month_start, INTERVAL 1 MONTH)
                                FROM month_list
                                WHERE month_start > DATE_SUB(DATE_FORMAT(CURDATE(), '%Y-%m-01'), INTERVAL 1 YEAR)
                            )

                            select DATE_FORMAT(d.month_start, '%Y-%m') as time,IFNULL(amt.saleamt,0) as sellAmount,IFNULL(amt.profit,0) as profitAmount 
                            from month_list as d
                            left join (
                            select t.profit,t.saleAmt,CONCAT(t.time,'-01') as time from(
                            select 
                            sum(c.amount) as profit,
                            sum(c2.goodsAmount) as saleAmt,
                            DATE_FORMAT(c.createTime, '%Y-%m')  as time
                            from  corecmsagentorder c 
                            inner join corecmsorder c2 
                            on c.orderId = c2.orderId 
                            where c.isSettlement=1
                            and c.isDelete=0
                            and c.createTime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                            and c.createTime<=CONCAT(CURDATE(), ' ', MAKETIME(23, 59, 59))
                            and c.UserId={userId}
                            group by DATE_FORMAT(c.createTime, '%Y-%m')
                            ) as t
                            ) as amt on d.month_start=amt.`time`");
            }
            else if (timeType == 2)//按季度
            {
                sb.Append($@"WITH RECURSIVE quarter_list AS (
                                SELECT 
                                    MAKEDATE(YEAR(CURDATE()), 1) + INTERVAL QUARTER(CURDATE()) * 3 - 3 MONTH AS quarter_start
                                UNION ALL
                                SELECT 
                                    DATE_SUB(quarter_start, INTERVAL 3 MONTH)
                                FROM 
                                    quarter_list
                                WHERE 
                                    quarter_start > DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                            )
                            select DATE_FORMAT(d.quarter_start, '%Y-%m') as time,IFNULL(amt.saleamt,0) as sellAmount,IFNULL(amt.profit,0) as profitAmount 
                            from quarter_list as d
                            left join (
                            select t.profit,t.saleAmt,CONCAT(t.time,'-01') as time from(
                            select 
                            sum(c.amount) as profit,
                            sum(c2.goodsAmount) as saleAmt,
                            DATE_FORMAT(c.createTime, '%Y-%m')  as time
                            from  corecmsagentorder c 
                            inner join corecmsorder c2 
                            on c.orderId = c2.orderId 
                            where c.isSettlement=1
                            and c.isDelete=0
                            and c.createTime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                            and c.createTime<=CONCAT(CURDATE(), ' ', MAKETIME(23, 59, 59))
                            and c.UserId={userId}
                            group by DATE_FORMAT(c.createTime, '%Y-%m')
                            ) as t
                            ) as amt on d.quarter_start=amt.`time`");
            }
            else if (timeType == 3)
            {
                sb.Append($@"WITH RECURSIVE year_list AS (
                                SELECT YEAR(CURDATE()) AS year
                                UNION ALL
                                SELECT year - 1
                                FROM year_list
                                WHERE year > YEAR(CURDATE()) - 1
                            )
                            select d.year as time,IFNULL(amt.saleamt,0) as sellAmount,IFNULL(amt.profit,0) as profitAmount 
                            from year_list as d
                            left join (
                            select t.profit,t.saleAmt,CONCAT(t.time,'') as time from(
                            select 
                            sum(c.amount) as profit,
                            sum(c2.goodsAmount) as saleAmt,
                            DATE_FORMAT(c.createTime, '%Y')  as time
                            from  corecmsagentorder c 
                            inner join corecmsorder c2 
                            on c.orderId = c2.orderId 
                            where c.isSettlement=1
                            and c.isDelete=0
                            and c.createTime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                            and c.createTime<=CONCAT(CURDATE(), ' ', MAKETIME(23, 59, 59))
                            and c.UserId={userId}
                            group by DATE_FORMAT(c.createTime, '%Y')
                            ) as t
                            ) as amt on d.year=amt.`time`");
            }
            else
            {
                return new List<SaleTotalByTimeTypeModel>();
            }
            return Context.Ado.SqlQuery<SaleTotalByTimeTypeModel>(sb.ToString());
        }

        public List<SaleTotalByItemTypeModel> GetSaleSummaryByItemType(long userId)
        {
            var sb = new StringBuilder();
            sb.Append($@"
                select 
                sum(c.amount) as totalProfits,
                sum(c2.goodsAmount-c.amount) as totalCosts,
                sum(c2.goodsAmount) as totalSales,
                0 as selfCosts,
                0 as selfProfits,
                0 as selfSales,
                0 as agencyCosts,
                0 as agencyProfits,
                0 as agencySales
                from  corecmsagentorder c 
                inner join corecmsorder c2 
                on c.orderId = c2.orderId 
                where c.isSettlement=1
                and c.isDelete=0
                and c.createTime>DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
                and c.createTime<=CONCAT(CURDATE(), ' ', MAKETIME(23, 59, 59))
                and c.UserId={userId}
           ");
            return Context.Ado.SqlQuery<SaleTotalByItemTypeModel>(sb.ToString());
        }


        public List<SaleOpdata> GetSaleData(CorecmsagentorderQueryDto param)
        {
            string where = string.Empty;
            if (param.BeginCreateTime != null)
            {
                where+=($@" and c.createTime>='{param.BeginCreateTime.Value.ToString("yyyy-MM-dd")}' ");
            }
            if (param.EndCreateTime != null)
            {
                where += ($@" and c.createTime<='{param.EndCreateTime.Value.ToString("yyyy-MM-dd")}' ");

            }
            StringBuilder sb = new StringBuilder();
            sb.AppendFormat(@"select u.areaNames,u.userName,u.accountCode,
                count(distinct o.orderId) as orders,
                sum(o2.nums) as nums,
                sum(ifnull(o.orderAmount,0)) as amt,
                ifnull(af.retnums,0) as retNums,
                sum(o2.nums)-ifnull(af.retnums,0) as realNums,
                ifnull(af.retCount,0) as retCount,
                ifnull(af.refundAmount,0) as retAmt,
                s.userName as saleMan
                from corecmsuser u
                left join sale_man s on u.saleManId=s.id
                left join corecmsorder o on u.Id=o.userId and o.status=2
                left join (select sum(nums) as nums,orderId from corecmsorderitem group by orderId) as o2 on o.orderId=o2.orderId
                left join (select c.userId,count(distinct c.orderId) as retCount,sum(c2.nums) as retnums,sum(c2.amount) as refundAmount from corecmsbillaftersales c left join corecmsbillaftersalesitem c2 
                             on c.aftersalesId  =c2.aftersalesId where c.status =2 {0} group by c.userId) af on u.Id=af.userId
                where u.parentId=@userId ", where);
            if (!string.IsNullOrEmpty(param.Name)) 
            {
                sb.Append($@" and (u.userName like '{param.Name}%' or s.userName like '{param.Name}%') ");
            }
            if (param.BeginCreateTime != null) 
            { 
                sb.Append($@" and o.createTime>='{param.BeginCreateTime.Value.ToString("yyyy-MM-dd")} 00:00:00' ");
            }
            if (param.EndCreateTime != null) 
            {
                sb.Append($@" and o.createTime<='{param.EndCreateTime.Value.ToString("yyyy-MM-dd")} 23:59:59' ");

            }
            sb.Append(@"group by u.id,u.areaNames,u.userName,u.accountCode");


            return Context.Ado.SqlQuery<SaleOpdata>(sb.ToString(), new { userId = param.UserId });
        }

        /// <summary>
        /// 销售明细分页查询
        /// </summary>
        /// <param name="request"></param>
        /// <returns></returns>
        public PagedInfo<RptSaleDetail> GetRptSaleDetailPageList(RptSaleDetailQueryDto request)
        {
            var querySql = @"select 
                             c.createTime as orderTime,
                             (case when c.orderType=1 then '销售订单' else '' end) as orderType,
                             c.orderId as orderId,
                             c2.sn,
                             CONCAT(c2.name,'-',c3.skuName) as name,
                             c4.userName,
                             c3.spesDesc,
                             (c2.nums-ifnull(c8.retnums,0)) as nums,
                             c2.sendNums,(c2.nums-ifnull(c8.retnums,0)) as sumNums,
                             c2.price,(c2.amount-ifnull(c8.refundAmount,0)) as amount,
                             c2.price as saleTaxPrice,
                             (c2.amount-ifnull(c8.refundAmount,0)) as saleTaxAmount,
                             (c2.amount-(c2.nums-ifnull(c8.retNums,0)*commission)) as saleCostAmount,
                             (c2.amount-ifnull(c8.refundAmount,0)) as saleGrossAmount,
                             ((c2.nums-ifnull(c8.retnums,0))*c2.commission) as saleProfitAmount
                             from corecmsorder as c
                             left join corecmsorderitem as c2  on c.orderId =c2.orderId
                             left join corecmsgoods as goods on goods.id =c2.goodsId
                             left join corecmsproducts as c3  on c2.goodsId =c3.goodsId and c2.productId =c3.id
                             left join corecmsuser as c4  on c.userId =c4.id
                             left join corecmsuserstock c7 on c.userId =c7.userId and c2.goodsId =c7.goodId and c2.productId =c7.productId 
                             left join (select a.orderId,ai.productId,sum(ai.nums) as retnums,sum(ai.amount) as refundAmount from corecmsbillaftersales a 
                             left join corecmsbillaftersalesitem ai 
                             on a.aftersalesId  =ai.aftersalesId where a.afterProgress=3 and a.retReasons<>'3'  group by a.orderId,ai.productId ) as c8 on c2.orderId=c8.orderId and c2.productId =c8.productId 

                             where c.orderType =1 and c.status =2 and c2.nums-ifnull(c8.retnums,0)>0 
                             {0} ";


            var sbwhere = new StringBuilder();
            var sbwhere2 = new StringBuilder();
            if (!string.IsNullOrEmpty(request.goodsCategoryId) && request.goodsCategoryId != "0")
            {
                sbwhere.AppendFormat($@" and goods.categoryArr like '{request.goodsCategoryId}%' ");
            }

            if (request.createTime != null && request.createTime.Length == 1)
            {
                var daterange = request.createTime[0].Split(',');
                var startDate = DateTime.Parse(daterange[0]).ToString("yyyy-MM-dd HH:mm:ss");
                var endDate = DateTime.Parse(daterange[1] + " 23:59:59.997").ToString("yyyy-MM-dd HH:mm:ss.fff");
                sbwhere.AppendFormat($@" and c.createTime>='{startDate}' and c.createTime<='{endDate}'");
            }
            if (request.agentId > 0)
            {
                sbwhere.AppendFormat($@" and (c4.parentId = {request.agentId}) ");
            }

            if (!string.IsNullOrEmpty(request.productName))
            {
                sbwhere.AppendFormat($@" and (c3.skuName like '{request.productName}%')");
            }
            if (!string.IsNullOrEmpty(request.barCode))
            {
                sbwhere.AppendFormat($@" and (c3.barcode = '{request.barCode}')");
            }
            if (!string.IsNullOrEmpty(request.goodsName))
            {
                sbwhere.AppendFormat($@" and (c3.sn = '{request.goodsName}')");
            }
            if(!string.IsNullOrEmpty(request.userName))
            {
                sbwhere.AppendFormat($@" and c4.userName like '%{request.userName}%'");
            }
            if(!string.IsNullOrEmpty(request.orderId))
            {
                sbwhere.AppendFormat($@" and c.orderId like '{request.orderId}%'");
            }

            querySql = string.Format(querySql, sbwhere.ToString());
            var orderfield = request.orderField;

            var pageSql = $@"SELECT * FROM ({querySql}) as subquery
                     ORDER BY {orderfield} {request.SortType.ToString()}
                     LIMIT {(request.PageNum - 1) * request.PageSize}, {request.PageSize}";
            var countSql = $@"SELECT COUNT(*) FROM ({querySql}) as subquery";


            var list = Context.Ado.SqlQuery<RptSaleDetail> (pageSql).ToList();
            RefAsync<int> totalCount = Context.Ado.GetInt(countSql);
            var page = new PagedInfo<RptSaleDetail>();

            page.TotalNum = totalCount;
            page.PageSize = request.PageSize;
            page.PageIndex = request.PageNum;
            page.Result = list;
            //var list2 = new PageList<CustomerSaleDetail>(list, pageIndex, pageSize, totalCount);
            return page;
        }

        /// <summary>
        /// 销售汇总分页查询
        /// </summary>
        /// <param name="requerst"></param>
        /// <returns></returns>
        public PagedInfo<RptSaleSummary> GetRptSaleSummaryPageList(RptSaleSummaryQueryDto request)
        {
            var querySql = @"select t.*,c.userName ,c.accountCode as userCode 
                                from 
                                (
                                select c.userId,SUM(ifnull(c2.nums-ifnull(c8.nums,0),0)) as realSaleNums,
                                SUM(c2.nums) as saleNums,
                                SUM(ifnull(c2.nums-ifnull(c8.nums,0),0))/SUM(c2.nums) as salePercent,
                                SUM(ifnull(c8.nums,0))/SUM(c2.nums) as backPercent,
                                SUM(c2.amount-ifnull(c8.refundAmount,0)) as realSaleAmount,
                                SUM(c2.amount-ifnull(c8.refundAmount,0)) as realTaxSaleAmount,
                                SUM(c2.amount-(c2.nums-ifnull(c8.nums,0)*commission)) as saleCostAmount,
                                SUM((c2.nums-ifnull(c8.nums,0))*c2.commission) as saleGrossAmount
                                from corecmsorder c 
                                left join corecmsorderitem c2  on c.orderId =c2.orderId
                                left join corecmsgoods as goods on goods.id =c2.goodsId
                                left join corecmsuser as c7  on c.userId =c7.id
                                left join corecmsuserstock c6 on c6.goodId =c2.goodsId and c6.productId =c2.productId and c.userId =c6.userId 
                                left join corecmsproducts as c5  on c2.goodsId =c5.goodsId and c2.productId =c5.id
                                left join (select a.orderId,ai.productId,sum(ai.nums) as nums,sum(ai.amount) as refundAmount from corecmsbillaftersales a 
                                left join corecmsbillaftersalesitem ai  on a.aftersalesId  =ai.aftersalesId where a.afterProgress=3 and a.retReasons<>'3'  group by a.orderId,ai.productId ) as c8 on c2.orderId=c8.orderId and c2.productId =c8.productId 
                                where c.orderType =1 and c.status =2
                                {0}
                                group by c.userId
                                ) as t
                                left join corecmsuser c on t.userid =c.id
                                where 1=1 {1} ";


            var sbwhere = new StringBuilder();
            var sbwhere2 = new StringBuilder();
            if (!string.IsNullOrEmpty(request.goodsCategoryId) && request.goodsCategoryId != "0")
            {
                sbwhere.AppendFormat($@" and goods.categoryArr like '{request.goodsCategoryId}%' ");
            }

            if (request.createTime != null && request.createTime.Length == 1)
            {
                var daterange = request.createTime[0].Split(',');
                var startDate = DateTime.Parse(daterange[0]).ToString("yyyy-MM-dd HH:mm:ss");
                var endDate = DateTime.Parse(daterange[1] + " 23:59:59.997").ToString("yyyy-MM-dd HH:mm:ss.fff");
                sbwhere.AppendFormat($@" and c.createTime>='{startDate}' and c.createTime<='{endDate}'");
            }

            if (request.agentId > 0)
            {
                sbwhere.AppendFormat($@" and (c7.parentId = {request.agentId}) ");
            }
            if (!string.IsNullOrEmpty(request.productName))
            {
                sbwhere.AppendFormat($@" and (c5.skuName like '{request.productName}%')");
            }
            if (!string.IsNullOrEmpty(request.barCode))
            {
                sbwhere.AppendFormat($@" and (c5.barcode = '{request.barCode}')");
            }
            if (!string.IsNullOrEmpty(request.goodsName))
            {
                sbwhere.AppendFormat($@" and (c5.sn = '{request.goodsName}')");
            }
            if (!string.IsNullOrEmpty(request.userName))
            {
                sbwhere.AppendFormat($@" and c7.userName like '%{request.userName}%'");
            }

            querySql = string.Format(querySql, sbwhere.ToString(), sbwhere2.ToString());
            var orderfield = request.orderField;

            var pageSql = $@"SELECT * FROM ({querySql}) as subquery
                     ORDER BY {orderfield} {request.SortType.ToString()}
                     LIMIT {(request.PageNum - 1) * request.PageSize}, {request.PageSize}";
            var countSql = $@"SELECT COUNT(*) FROM ({querySql}) as subquery";

            var list = Context.Ado.SqlQuery<RptSaleSummary>(pageSql).ToList();
            RefAsync<int> totalCount = Context.Ado.GetInt(countSql);
            var page = new PagedInfo<RptSaleSummary>();

            page.TotalNum = totalCount;
            page.PageSize = request.PageSize;
            page.PageIndex = request.PageNum;
            page.Result = list;
            return page;
        }

    }
}
